﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace SendAlert
{
    class Database
    {        
        public int descarcaUseriCuAlarme()
        {
            SqlConnection conn = new SqlConnection("Data Source=mail.pioneerinvestments.ro\\sql2008,1333;Initial Catalog=Cotrobaie;Persist Security Info=True;User ID=sa;Password=sa1234");                

            SqlDataReader readerUseri;

            SqlCommand comanda = new SqlCommand("SELECT DISTINCT Subscribe.user_id, Useri.Nume + ' ' + Useri.Prenume AS nume, Useri.[E-mail] As email FROM Subscribe INNER JOIN Useri ON Subscribe.user_id = Useri.ID", conn);

            try
            {
                conn.Open();
                readerUseri = comanda.ExecuteReader();
            }
            catch (Exception e)
            {
                return -1;
            }

            while ( readerUseri.Read() )
            {
                string userCurent = readerUseri["user_id"].ToString();
                string continutMail = descarcaProduse(userCurent);
                if(continutMail != "") TrimitereMail.TrimiteMailReducere(readerUseri["nume"].ToString(), readerUseri["email"].ToString(), continutMail);
            }

            readerUseri.Dispose();
            conn.Close();
            return 0;
        }

        public int updateEmailSent(string user_id, string pret)
        {
            SqlConnection conn = new SqlConnection("Data Source=mail.pioneerinvestments.ro\\sql2008,1333;Initial Catalog=Cotrobaie;Persist Security Info=True;User ID=sa;Password=sa1234");
            SqlCommand comanda = new SqlCommand("UPDATE subscribe SET email_sent = 1 where email_sent is null and user_id = " + user_id + " and price = "+pret+"", conn);
            comanda.CommandType = System.Data.CommandType.Text;
            
            try
            {
                conn.Open();
                comanda.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                return -1;
            }

            return 0;
        }
        public string descarcaProduse(string user_id)
        {
            SqlConnection conn = new SqlConnection("Data Source=mail.pioneerinvestments.ro\\sql2008,1333;Initial Catalog=Cotrobaie;Persist Security Info=True;User ID=sa;Password=sa1234");            
            SqlCommand comanda = new SqlCommand("SELECT ISNULL(alertPrice, 0) AS alertPrice, ISNULL([percent], 0) AS procent, ISNULL(price, 0) AS price, product_id, user_id FROM Subscribe where email_sent is null and user_id = "+user_id+"", conn);
            comanda.CommandType = System.Data.CommandType.Text;

            SqlDataReader reader;
            try
            {
                conn.Open();
                reader = comanda.ExecuteReader();
            }
            catch (Exception e)
            {
                return "";
            }

            String continutMail = "";

            float alertPrice, percent, price;
            int product_id;

            while (reader.Read())
            {
                alertPrice = float.Parse(reader["alertPrice"].ToString());
                percent = float.Parse(reader["procent"].ToString());
                price = float.Parse(reader["price"].ToString());
                product_id = int.Parse(reader["product_id"].ToString());
                continutMail += CautaPretMaiMic(alertPrice, percent, price, product_id, user_id);
            }

            reader.Dispose();
            conn.Close();
            return continutMail;
        }

        public string CautaPretMaiMic(float alertPrice, float percent, float price, int product_id, string user_id)
        {
            string adaugareContinut = "";

            SqlConnection conn = new SqlConnection("Data Source=mail.pioneerinvestments.ro\\sql2008,1333;Initial Catalog=Cotrobaie;Persist Security Info=True;User ID=sa;Password=sa1234");

            float alertPriceConsiderat;

            if (alertPrice > 0) alertPriceConsiderat = alertPrice;
            else alertPriceConsiderat = price - (price * percent / 100);

            SqlCommand comanda = new SqlCommand(
                                 " SELECT TOP (1) ID, price, product_id, site_id, timestamp FROM Price WHERE   " +
                                 "    (ID > " +
                                 "    (SELECT TOP (1) ID " +
                                 "     FROM Price AS Price_1 " +
                                 "     WHERE        (price = " + price + ") AND (product_id = " + product_id + ") " +
                                 "     ORDER BY timestamp DESC)) " +
                                 " AND (product_id = " + product_id +") AND (price <= " + alertPriceConsiderat + ") " +
                                 " ORDER BY timestamp ", conn);

            SqlDataReader reader;

            try
            {
                conn.Open();
                reader = comanda.ExecuteReader();
            }
            catch (Exception e)
            {
                return "";
            }

            // Daca reader-ul intoarce un rand inseamna ca a gasit cel putin un pret mai mic sau egal decat alertPrice 
            while (reader.Read())
            {
                float priceGasit = float.Parse(reader["price"].ToString());
                //TrimitereMail.TrimiteMailReducere(

                SqlConnection conn2 = new SqlConnection("Data Source=mail.pioneerinvestments.ro\\sql2008,1333;Initial Catalog=Cotrobaie;Persist Security Info=True;User ID=sa;Password=sa1234");
                SqlCommand comandaProdus = new SqlCommand("SELECT name FROM Product WHERE id = " + product_id+"", conn2);
                SqlDataReader readerProdus;

                string produs = "";
                try
                {
                    conn2.Open();
                    readerProdus = comandaProdus.ExecuteReader();
                    while (readerProdus.Read())
                    {
                        produs = readerProdus["name"].ToString();
                    }
                }
                catch (Exception e) { return ""; }

                adaugareContinut += "<br/>Produsul " + produs + " a fost redus la pretul de " + priceGasit + " . "
                + "<br/> Acceseaza pagina produsului pe urmatorul <a href='http://cotrobaie.cjb.net//product.aspx?ProductID=" + product_id + "' title='click'>link</a><br/><br/>";

                // Trebuie sa realizez update-ul pentru coloana email_sent din tabela Subscribe
                updateEmailSent(user_id,price.ToString());

                readerProdus.Dispose();
                conn2.Close();
            }

            reader.Dispose();
            conn.Close();
            return adaugareContinut;
        }
    }
}
